{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "\n",
    "sc = (SparkSession.builder.appName('app15-3') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ut_staff = sc.read.table('sqlzoo.ut_staff')\n",
    "ut_student = sc.read.table('sqlzoo.ut_student')\n",
    "ut_event = sc.read.table('sqlzoo.ut_event')\n",
    "ut_room = sc.read.table('sqlzoo.ut_room')\n",
    "ut_attends = sc.read.table('sqlzoo.ut_attends')\n",
    "ut_teaches = sc.read.table('sqlzoo.ut_teaches')\n",
    "ut_occurs = sc.read.table('sqlzoo.ut_occurs')\n",
    "ut_modle = sc.read.table('sqlzoo.ut_modle')\n",
    "ut_week = sc.read.table('sqlzoo.ut_week')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**co.CHt is to be given all the teaching that co.ACg currently does. Identify those events which will clash.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>co.Cht</th>\n",
       "      <th>co.ACg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co12005.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co72013.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co72013.T03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        co.Cht       co.ACg\n",
       "0  co12005.T03  co12005.T01\n",
       "1  co12005.T03  co72013.L02\n",
       "2  co12005.T03  co72013.T03"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.withColumnRenamed('id', 'event')\n",
    "     .join(ut_teaches, on='event')\n",
    "     .join(ut_occurs, on='event')\n",
    "     .withColumn('begin', to_timestamp(col('tod'), format='HH:mm'))\n",
    "     .withColumn('end', (unix_timestamp(col('tod'), format='HH:mm') + \n",
    "                 (3600 * col('duration'))).cast('timestamp')))\n",
    "\n",
    "(t.filter(col('staff')=='co.CHt').alias('t1')\n",
    " .join(t.filter(col('staff')=='co.ACg').alias('t2'), \n",
    "       on=['week', 'dow'])\n",
    " .filter(((col('t1.begin') >= col('t2.begin')) & \n",
    "          (col('t1.begin') < col('t2.end'))) |\n",
    "         ((col('t2.begin') >= col('t1.begin')) & \n",
    "          (col('t2.begin') < col('t1.end'))))\n",
    " .select(col('t1.event').alias('co.Cht'), \n",
    "         col('t2.event').alias('co.ACg'))\n",
    " .distinct()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Produce a table showing the utilisation rate and the occupancy level for all rooms with a capacity more than 60.**\n",
    "\n",
    "> I don't know how 'utilisation rate' and 'occupancy level' are defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room</th>\n",
       "      <th>util</th>\n",
       "      <th>occup_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cr.SMH</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.274574</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     room  util  occup_level\n",
       "0  cr.SMH  0.24     0.274574"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# theoretical full utilisation hours: 15 weeks * 5 days * 12 hours\n",
    "close_time = (ut_event\n",
    "              .withColumn('close_time', \n",
    "                          (unix_timestamp(col('tod'), format='HH:mm') + \n",
    "                          3600*col('duration')).cast('timestamp'))\n",
    "              .agg({'close_time': 'max'}))\n",
    "open_time = (ut_event\n",
    "             .withColumn('open_time', \n",
    "                         to_timestamp(col('tod'), format='HH:mm'))\n",
    "             .agg({'open_time': 'min'}))\n",
    "max_hrs = (close_time.collect()[0][0] -\n",
    "           open_time.collect()[0][0]).seconds // 3600\n",
    "t = ut_week.withColumn('max_hrs', 5 * lit(max_hrs))\n",
    "\n",
    "# attended students per event\n",
    "s = (ut_event.withColumnRenamed('id', 'event')\n",
    "     .join(ut_occurs, on='event')\n",
    "     .join(ut_attends, on='event')\n",
    "     .join(ut_student, on=(ut_attends['student']==ut_student['id']))\n",
    "     .join(ut_room, on=(ut_event['room']==ut_room['id']))\n",
    "     .groupBy('room')\n",
    "     .agg(sum('sze'), sum('capacity')))\n",
    "\n",
    "(ut_event.withColumnRenamed('id', 'event')\n",
    " .join(ut_occurs, on='event')\n",
    " .join(ut_room.filter(col('capacity') > 60), \n",
    "       on=(ut_event['room']==ut_room['id']))\n",
    " .groupBy('room')\n",
    " .sum('duration')\n",
    " .join(s, on='room')\n",
    " .withColumn('util', col('sum(duration)') /\n",
    "             t.groupBy().sum('max_hrs').collect()[0][0])\n",
    " .withColumn('occup_level', col('sum(sze)')/col('sum(capacity)'))\n",
    " .select('room', 'util', 'occup_level')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**A one hour staff meeting is to be held between 09:00 and 17:00. Events which clash are to be cancelled. Identify the hour which will result in the least disruption.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dow</th>\n",
       "      <th>hour</th>\n",
       "      <th>01</th>\n",
       "      <th>02</th>\n",
       "      <th>03</th>\n",
       "      <th>04</th>\n",
       "      <th>05</th>\n",
       "      <th>06</th>\n",
       "      <th>07</th>\n",
       "      <th>08</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dow  hour  01  02  03  04  05  06  07  08  10  11  12  13\n",
       "0    0     9   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "1    0    10   1   1   1   1   1   1   1   1   1   1   1   1\n",
       "2    0    11   8   8   8   8   8   8   8   8   8   8   8   8\n",
       "3    0    12   3   3   3   3   3   3   3   3   3   3   3   3\n",
       "4    0    13  10  10  10  10  10  10  10  10  10  10  10  10\n",
       "5    0    14   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "6    0    15  10  10  10  10  10  10  10  10  10  10  10  10\n",
       "7    0    16   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "8    0    17   8   8   8   8   8   8   8   8   8   8   8   8\n",
       "9    0    18   1   1   1   1   1   1   1   1   1   1   1   1\n",
       "10   1     9   6   6   6   6   6   6   6   6   6   6   6   6\n",
       "11   1    10   7   7   7   7   7   7   7   7   7   7   7   7\n",
       "12   1    11  10  10  10  10  10  10  10  10  10  10  10  10\n",
       "13   1    12  10  10  10  10  10  10  10  10  10  10  10  10\n",
       "14   1    13  11  11  11  11  11  11  11  11  11  11  11  11\n",
       "15   1    14   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "16   1    15  10  10  10  10  10  10  10  10  10  10  10  10\n",
       "17   1    16   8   8   8   8   8   8   8   8   8   8   8   8\n",
       "18   1    17   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "19   1    18   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "20   1    20   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "21   2     9   8   8   8   8   8   8   8   8   8   8   8   8\n",
       "22   2    10   3   3   3   3   3   3   3   3   3   3   3   3\n",
       "23   2    11   8   8   8   8   8   8   8   8   8   8   8   8\n",
       "24   2    12   7   7   7   7   7   7   7   7   7   7   7   7\n",
       "25   2    13   6   6   6   6   6   6   6   6   6   6   6   6\n",
       "26   2    14   6   6   6   6   6   6   6   6   6   6   6   6\n",
       "27   2    15   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "28   2    16   7   7   7   7   7   7   7   7   7   7   7   7\n",
       "29   2    17   3   3   3   3   3   3   3   3   3   3   3   3\n",
       "30   2    18   2   2   2   2   2   2   2   2   2   2   2   2\n",
       "31   3     9   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "32   3    10   4   4   4   4   4   4   4   4   4   4   4   4\n",
       "33   3    11  11  11  11  11  11  11  11  11  11  11  11  11\n",
       "34   3    12   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "35   3    13   9   9   9   9   9   9   9   9   9   9   9   9\n",
       "36   3    14   7   7   7   7   7   7   7   7   7   7   7   7\n",
       "37   3    15  11  11  11  11  11  11  11  11  10  10  10  10\n",
       "38   3    16   6   6   6   6   6   6   6   6   6   6   6   6\n",
       "39   3    17   8   8   8   8   8   8   8   8   7   7   7   7\n",
       "40   3    18   1   1   1   1   1   1   1   1   1   1   1   1\n",
       "41   4     9   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "42   4    10   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "43   4    11   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "44   4    12   5   5   5   5   5   5   5   5   5   5   5   5\n",
       "45   4    13   7   7   7   6   6   6   6   6   6   6   6   6\n",
       "46   4    14   3   3   3   3   3   3   3   3   4   4   4   4\n",
       "47   4    15   7   7   7   7   7   7   7   7   7   7   7   7\n",
       "48   4    16   4   4   4   4   3   3   3   3   4   4   4   4\n",
       "49   4    17   6   6   6   6   6   6   6   6   6   6   6   6"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.withColumn('tod', to_timestamp(ut_event['tod'], format='HH:mm'))\n",
    "     .withColumn('duration', ut_event['duration'].cast('integer'))\n",
    "     .join(ut_occurs, on=(ut_event['id']==ut_occurs['event']))\n",
    "     .select('event', 'week', 'dow', 'tod', 'duration'))\n",
    "\n",
    "for i in range(2, 10):\n",
    "    t_ = t.filter(t['duration']==i)\n",
    "    if t_.count() > 0:\n",
    "        t_ = t_.withColumn('tod', t_['tod'] + expr(f'interval {i} hours'))\n",
    "        t = t.union(t_)\n",
    "\n",
    "(t.replace({'Monday': '0', 'Tuesday': '1', 'Wednesday': '2', \n",
    "            'Thursday': '3', 'Friday': '4'}, subset=['dow'])\n",
    " .withColumn('hour', hour(col('tod')))\n",
    " .groupBy('dow', 'hour')\n",
    " .pivot('week')\n",
    " .count()\n",
    " .orderBy('dow', 'hour')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tod</th>\n",
       "      <th>dow</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10:00</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     tod     dow  event\n",
       "0  10:00  Monday     12"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You may find that week '09', '14', '15' are totally vacant\n",
    "# or you can choose Mon 9am/12pm, Tue 5pm, Thu 9am, Fri 5pm\n",
    "\n",
    "(t.withColumn('tod', date_format(t['tod'], 'HH:mm'))\n",
    " .filter(hour(col('tod')).between(9, 17))\n",
    " .groupBy('tod', 'dow')\n",
    " .agg(count('event').alias('event'))\n",
    " .orderBy('event', 'tod', 'dow')\n",
    " .limit(1)\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Find all clashes - include the events which clash and the staff, student or rooms that they have in common.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>event1</th>\n",
       "      <th>event2</th>\n",
       "      <th>room</th>\n",
       "      <th>week</th>\n",
       "      <th>dow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>11</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>02</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>08</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>03</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>10</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>12</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>04</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>01</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>05</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>06</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>07</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>13</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>01</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>12</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>02</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>07</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>06</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>03</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>04</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>13</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>08</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>10</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>11</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>05</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            event1       event2        room week      dow\n",
       "0   coh8412615.T03  co22008.T04  co.117+118   11  Tuesday\n",
       "1   coh8412615.T03  co22008.T04  co.117+118   02  Tuesday\n",
       "2   coh8412615.T03  co22008.T04  co.117+118   08  Tuesday\n",
       "3   coh8412615.T03  co22008.T04  co.117+118   03  Tuesday\n",
       "4   coh8412615.T03  co22008.T04  co.117+118   10  Tuesday\n",
       "5   coh8412615.T03  co22008.T04  co.117+118   12  Tuesday\n",
       "6   coh8412615.T03  co22008.T04  co.117+118   04  Tuesday\n",
       "7   coh8412615.T03  co22008.T04  co.117+118   01  Tuesday\n",
       "8   coh8412615.T03  co22008.T04  co.117+118   05  Tuesday\n",
       "9   coh8412615.T03  co22008.T04  co.117+118   06  Tuesday\n",
       "10  coh8412615.T03  co22008.T04  co.117+118   07  Tuesday\n",
       "11  coh8412615.T03  co22008.T04  co.117+118   13  Tuesday\n",
       "12     co22020.T04  co22007.T01     co.LB47   01   Friday\n",
       "13     co22020.T04  co22007.T01     co.LB47   12   Friday\n",
       "14     co22020.T04  co22007.T01     co.LB47   02   Friday\n",
       "15     co22020.T04  co22007.T01     co.LB47   07   Friday\n",
       "16     co22020.T04  co22007.T01     co.LB47   06   Friday\n",
       "17     co22020.T04  co22007.T01     co.LB47   03   Friday\n",
       "18     co22020.T04  co22007.T01     co.LB47   04   Friday\n",
       "19     co22020.T04  co22007.T01     co.LB47   13   Friday\n",
       "20     co22020.T04  co22007.T01     co.LB47   08   Friday\n",
       "21     co22020.T04  co22007.T01     co.LB47   10   Friday\n",
       "22     co22020.T04  co22007.T01     co.LB47   11   Friday\n",
       "23     co22020.T04  co22007.T01     co.LB47   05   Friday"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event\n",
    "     .withColumn('start', to_timestamp(ut_event['tod'], format='HH:mm'))\n",
    "     .withColumnRenamed('id', 'event')\n",
    "     .join(ut_occurs, on='event')\n",
    "     .withColumn('end', (unix_timestamp(col('start')) + 3600*col('duration'))\n",
    "                 .cast('timestamp')))\n",
    "(t.alias('t1').join(t.alias('t2'), on=['week', 'dow', 'room'])\n",
    " .filter(col('t1.event') != col('t2.event'))\n",
    " .filter((((col('t1.start') >= col('t2.start')) &\n",
    "           (col('t1.start') < col('t2.end'))) |\n",
    "          ((col('t2.start') >= col('t1.start')) &\n",
    "           (col('t2.start') < col('t1.end')))) &\n",
    "         (col('t1.event') > col('t2.event')))\n",
    " .select(col('t1.event').alias('event1'),\n",
    "         col('t2.event').alias('event2'),\n",
    "         'room', 'week', 'dow')\n",
    " .distinct()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Produce a timetable for a group of full time students for week 1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dow</th>\n",
       "      <th>student</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Friday</td>\n",
       "      <td>co.12008.Eb</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Friday</td>\n",
       "      <td>co.22022.E</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Friday</td>\n",
       "      <td>co1.CO</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Friday</td>\n",
       "      <td>co2.CO</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Friday</td>\n",
       "      <td>co2.CO.a</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>com.ITeC.feb</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>com.MM.feb</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>com.MM.pt3</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>com.SE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>com.ST</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>195 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           dow       student  9 10 11 12 13 14 15 16 17 18 20\n",
       "0       Friday   co.12008.Eb                 Y     Y         \n",
       "1       Friday    co.22022.E  Y  Y     Y                     \n",
       "2       Friday        co1.CO     Y                           \n",
       "3       Friday        co2.CO                    Y     Y      \n",
       "4       Friday      co2.CO.a  Y     Y     Y                  \n",
       "..         ...           ... .. .. .. .. .. .. .. .. .. .. ..\n",
       "190  Wednesday  com.ITeC.feb                 Y     Y         \n",
       "191  Wednesday    com.MM.feb  Y     Y  Y                     \n",
       "192  Wednesday    com.MM.pt3  Y     Y  Y                     \n",
       "193  Wednesday        com.SE                 Y     Y         \n",
       "194  Wednesday        com.ST                 Y     Y         \n",
       "\n",
       "[195 rows x 13 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event\n",
    "     .withColumn('tod', to_timestamp(ut_event['tod'], format='HH:mm'))\n",
    "     .withColumnRenamed('id', 'event')\n",
    "     .join(ut_occurs.filter(col('week')=='01'), on='event')\n",
    "     .join(ut_attends, on='event'))\n",
    "for i in range(2, 10):\n",
    "    t_ = t.filter(t['duration']==i)\n",
    "    if t_.count() > 0:\n",
    "        t_ = t_.withColumn('tod', t_['tod'] + expr(f'interval {i} hours'))\n",
    "        t = t.union(t_)\n",
    "\n",
    "(t.withColumn('tod', hour(col('tod')))\n",
    " .withColumn('flag', lit('Y'))\n",
    " .groupBy('dow', 'student')\n",
    " .pivot('tod')\n",
    " .agg(first('flag'))\n",
    " .fillna('')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
